package com.xsy.middleware.controller;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.xsy.middleware.service.DataBackService;
import com.xsy.middleware.utils.CrmApiUtils;
import com.xsy.middleware.utils.ResultVo;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;

import java.io.UnsupportedEncodingException;
import java.util.ArrayList;

/**
 * 初始化本地数据库
 */
@Slf4j
@Controller
public class InitCrmSyncTable {

    @Autowired
    private CrmApiUtils crmApiUtils;

    @Autowired
    private DataBackService dataBackService;

    @Autowired
    private SyncCrmTableFile syncCrmTableFile;

    public static void main(String[] args) {
     
    }

    /**
     * 初始化 - 批量建表同步数据、文件
     * @return
     */
    public ResultVo batchInitCrmSyncTable() {
        try {
            log.info("开始初始化本地数据库，同步数据、文件 ======>");

            // 获取没有建表的配置信息
            JSONArray dataBackList = dataBackService.getDataBackupByCreate(2);
            for (int i = 0; i < dataBackList.size(); i++) {
                JSONObject dataBack = dataBackList.getJSONObject(i);
                String tableName = dataBack.getString("objectApiKey__c");
                log.info("建表，同步数据、文件 开始:=====> {} - {} - {}", i, dataBackList.size(), tableName);
                createTable(tableName, 1);
                log.info("建表，同步数据、文件 结束:=====> {} - {} - {}", i, dataBackList.size(), tableName);
            }

            log.info("完成初始化本地数据库，同步数据、文件 ======>");
            return ResultVo.success("完成初始化本地数据库，同步数据、文件");
        } catch (UnsupportedEncodingException e) {
            //throw new RuntimeException(e);
            log.info("初始化本地数据库，同步数据、文件异常 ======> {}", e.getMessage());
            return ResultVo.error("初始化本地数据库，同步数据、文件异常: " + e.getMessage());
        }
    }


    /**
     * 创建数据库表格  ===> 用户表同步查询报错的字段: passwordRuleId isVirtual relatedDepart relatedProduct relatedArea relatedBusiness relatedIndustry (在crm表中设置不需同步)
     * @param tableName 表格名称 customEntity162__c
     * @param isSyncTableData 是否同步表数据 0：否 1: 是
     */
    public ResultVo createTable(String tableName, Integer isSyncTableData) {
        JSONObject tableData = new JSONObject();
        try {

            if (StringUtils.isBlank(tableName)) {
                log.info("配置表-表名不可为空");
                return ResultVo.error("配置表-表名不可为空");
            }

            // 同步文件的时间
            long syncDataTime = System.currentTimeMillis();

            // 查询配置表信息
            tableData = dataBackService.getDataBackup(tableName);
            if (tableData == null) {
                log.info("查询配置表数据为空");
                return ResultVo.error("查询配置表数据为空");
            }

            // 查询配置表明细
            JSONArray tableFieldList = dataBackService.getDataBackupDetailByType(tableData.getLong("id"), 1);
            if (tableFieldList.size() <= 0) {
                log.info("无配置表明细数据 : {}", tableName);
                return ResultVo.error("无配置表明细数据");
            }

            // 创建表
            tableName = tableData.getString("objectApiKey__c"); // 表名称
            String createTableSql = createTableSql(tableName, tableFieldList);
            log.info("createTableSql : {}", createTableSql);
            dataBackService.executeSql(createTableSql);

            // 是否同步表数据
            int count = 0;
            if (isSyncTableData == 1) {
                // 同步表数据
                ResultVo<Integer> syncDataVo = firstSyncCrmTableData(tableName, tableData);
                if (syncDataVo.getCode() != 200) {
                    return syncDataVo;
                }
                count = syncDataVo.getData();

                // 同步文件
                ResultVo syncFileVo = syncCrmTableFile.syncCrmTableFile(tableName, 0);
                if (syncFileVo.getCode() != 200) {
                    return syncFileVo;
                }
            }

            // 更新配置表状态 isCreateTable__c 是否建表  1: 是  2: 否
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("id", tableData.get("id"));
            jsonObject.put("isCreateTable__c", "1");
            jsonObject.put("lastSyncAddCount__c", count);
            jsonObject.put("lastSyncUpdateCount__c", 0);
            jsonObject.put("syncStatus__c", 1);
            jsonObject.put("syncErrorDesc__c", "");
            jsonObject.put("lastSyncTime__c", syncDataTime);
            jsonObject.put("lastSyncFileTime__c", syncDataTime);
            jsonObject.put("entityType", tableData.get("entityType"));
            ResultVo update = crmApiUtils.update("dataBackup__c", jsonObject);
            if (update.getCode() != 200) {
                log.info("更新配置表数据异常 : {}", update.getMsg());
            }

            return ResultVo.success( "数据同步成功 ===========> " + tableName);

        } catch (Exception e) {
            log.error("数据同步异常 : {}", e.getMessage());
            //throw new RuntimeException(e);

            try {
                // 更新配置表数据
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("id", tableData.get("id"));
                jsonObject.put("syncStatus__c", 2);
                jsonObject.put("syncErrorDesc__c", e.getMessage());
                System.out.println("jsonObject = " + jsonObject);
                dataBackService.updateDataBackup(jsonObject);
            } catch (UnsupportedEncodingException ex) {
                throw new RuntimeException(ex);
            }

            return ResultVo.error("数据同步异常: " + e.getMessage());
        }
    }

    /**
     * 根据表名首次批量同步表数据
     *
     * @param tableName
     * @param tableData
     * @return
     */
    public ResultVo<Integer> firstSyncCrmTableData(String tableName, JSONObject tableData) {
        try {

            // 查询需要同步的表数据 isSync__c 为1
            JSONArray tableFieldList = dataBackService.getDataBackupDetailByType(tableData.getLong("id"), 2);
            if (tableFieldList.size() <= 0) {
                log.info("配置表无需要同步的明细数据 : {}", tableName);
                return ResultVo.success(0);
            }

            // 获取需要查询的表语句
            String selectSql = getSelectSql(tableName, tableFieldList);
            log.info("selectSql : {}", selectSql);

            // 从 crm 获取表数据
            JSONArray tableDataList = crmApiUtils.getTableData(selectSql);
            log.info("tableDataList : {}", tableDataList.size());

            // 对数据进行保存到数据库 处理插入sql语句
            batchInsertData(tableName, tableFieldList, tableDataList);

            return ResultVo.success(tableDataList.size());
        } catch (UnsupportedEncodingException e) {
            return ResultVo.error("处理表数据失败" + e.getMessage());
        }
    }

    /**
     * 批量保存数据
     * @param tableName
     * @param tableFieldList
     * @param tableDataList
     * @return
     */
    public ResultVo batchInsertData(String tableName, JSONArray tableFieldList, JSONArray tableDataList) {

        // 先获取字段
        ArrayList<String> fieldList = new ArrayList<>();
        fieldList.add("id");
        for (Object o : tableFieldList) {
            JSONObject obj = (JSONObject) o;
            fieldList.add(obj.getString("fieldApiKey__c"));
        }

        // 先把原来的数据清空
        String delSql = " delete from " + tableName;
        dataBackService.executeSql(delSql);

        String insertSql = String.format("insert into %s (%s) values ", tableName, String.join(",", fieldList));

        ArrayList<String> insertSqlList = new ArrayList<>();
        for (int i = 0; i < tableDataList.size(); i++) {
            // 获取值
            JSONObject obj = tableDataList.getJSONObject(i);

            // 获取对应字段值
            ArrayList<String> fieldValueList = new ArrayList<>();
            for (String fieldKey : fieldList) {
                if (obj.get(fieldKey) != null) {
                    String fieldValue = obj.getString(fieldKey);
                    // 判断是否是反斜杠
                    if (fieldValue.length() == 1 && fieldValue.charAt(0) == '\\') {
                        fieldValueList.add("'\\\\'");
                    } else {
                        fieldValueList.add(StringUtils.isNotBlank(fieldValue) ? "'"  + fieldValue + "'" : null);
                    }
                    continue;
                }
                fieldValueList.add(null);
            }

            // 处理字段值
            String fieldValueJoin = String.join(",", fieldValueList);
            insertSqlList.add("(" + fieldValueJoin + ")");

            if (insertSqlList.size() >= 200 || i == tableDataList.size() -1) {
                // 200 条进行保存一次
                log.info("开始进行批量保存================================{}", i);
                dataBackService.executeSql(insertSql + String.join(",", insertSqlList));
                insertSqlList.clear();
            }

        }

        return ResultVo.success();
    }

    /**
     * 创建表语句
     * @param tableName
     * @param tableFieldList
     * @return
     */
    public String createTableSql(String tableName, JSONArray tableFieldList) {
        /*
            DROP TABLE IF EXISTS `file_record`;
            CREATE TABLE `file_record` (
              `id` varchar(32) NOT NULL,
              `source_id` varchar(32) DEFAULT NULL COMMENT '数据来源id',
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
        */
        StringBuilder stringBuilder = new StringBuilder();
        for (Object o : tableFieldList) {
            // fieldApiKey__c dbFieldType__c fieldName__c
            JSONObject obj = (JSONObject) o;
            stringBuilder.append(String.format("`%s` %s COMMENT '%s',\n", obj.get("fieldApiKey__c"), obj.get("dbFieldType__c"), obj.get("fieldName__c")));
        }
        String sql = "DROP TABLE IF EXISTS `%s`;\n " +
                    "CREATE TABLE `%s` (\n" +
                    "`id` varchar(32) NOT NULL,\n" +
                    "%s" +
                    "PRIMARY KEY (`id`)\n" +
                    ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;";
        return String.format(sql, tableName, tableName, stringBuilder);
    }

    /**
     * 获取查询的sql
     * @param tableFieldList
     * @return
     */
    public String getSelectSql(String tableName, JSONArray tableFieldList) {
        String sql = " select id,%s from %s";
        ArrayList<String> fieldList = new ArrayList<>();
        for (Object o : tableFieldList) {
            JSONObject obj = (JSONObject) o;
            fieldList.add(obj.getString("fieldApiKey__c"));
        }
        return String.format(sql, String.join(",", fieldList), tableName);
    }


}

